import pymongo
import xlrd


def readFile(filename):
    workbook = xlrd.open_workbook(filename)
    li = []
    for index in range(workbook.nsheets):
        sheet = workbook.sheet_by_index(index)
        document = readSheetAll(sheet)
        # print(document, '-----------------')
        if document is not None:
            li.append(document)
            # print(document['图号'])
    return li


def readSheetAll(sheet):
    if sheet.nrows <= 2:
        return
    doc = {
        "文件名称": sheet.cell_value(0, 1),
        "图号": sheet.cell_value(1, 1),
        "图纸名称": sheet.cell_value(2, 1),
        "产品型号": sheet.cell_value(3, 1),
        "材料名称": sheet.cell_value(4, 1),
        "数量": sheet.cell_value(5, 1),
        "重量": sheet.cell_value(6, 1),
        "设计日期": sheet.cell_value(7, 1),
        "子图": []
    }
    # print("图纸编号", sheet.cell_value(2, 1))
    # print('明细表', sheet.name)
    if sheet.nrows <= 8:
        print("行数", sheet.nrows)
        return
    for i in range(9, sheet.nrows):
        row = []
        for j in range(9):
            try:
                row.append(sheet.cell_value(i, j))
            except IndexError:
                print('list index out of range', row)
                return

        zitu = {
            '序号': row[0],
            '图号': row[1],
            '名称': row[2],
            '数量': row[3],
            '材料': row[4],
            '单重': row[5],
            '总重': row[6],
            '备注': row[7],
            '来源': row[8],
        }
        doc["子图"].append(zitu)
    return doc

# 生成树结构
def genTree(li):

    tuhaoList = []
    for jsonOne in li:
        print(jsonOne,'------------')
        print(jsonOne['图号'])
        # print(jsonOne['文件名称'])
        # print('====================',jsonOne['图号']==jsonOne['文件名称'])
        # 数据清洗：真操蛋 有些图号为空
        if jsonOne['图号'] is not None:
            if jsonOne['图号'].strip() == '':
                print('**************************')
                jsonOne['图号'] = jsonOne['文件名称']
            tuhaoList.append(jsonOne['图号'])

    # print(tuhaoList)
    # 小图列表
    xiaotuList = []
    # 单个sheet一个exb文件
    for jsonOne in li:
        # exb文件中明细栏
        for index, zitu in enumerate(jsonOne['子图']):
            # 明细栏中明细项 一行
            if zitu['图号'] in tuhaoList:  # 如果明细栏图号有一个exb文件；则追加到jsonOne[
                # print('*****')
                # print(jsonOne['子图'])
                xiaotuIndex = tuhaoList.index(zitu['图号'])
                jsonOne['子图'][index]['子图'] = list(li[xiaotuIndex]['子图'])
                # print(jsonOne['子图'][index])
                # print(zitu['子图'])
                # print(jsonOne)
                xiaotuList.append(li[xiaotuIndex]['图号'])
                # xiaotu.append(xiaotuIndex)
                #

                # zitu['子图']=list(jsonOne['子图'])
        # if jsonOne['图号'] == 'UZ22000(DY)':
        #     pprint(jsonOne)
        # print(json.dumps(jsonOne)
    #
    '''
    for index,jsonOne in enumerate(li):
        if jsonOne['图号'] in xiaotuList:
            li.pop(index)
    # return list(set(tuhaoList)-set(xiaotuList))
    '''
    newlist = []
    for index, jsonOne in enumerate(li):
        if jsonOne['图纸名称'] in '主轴承装配':
            newlist.append(jsonOne)

    return newlist

def saveTodb(collection,documents):
    myclient = pymongo.MongoClient('mongodb://192.168.10.36:27017/')
    mydb = myclient['pctt']
    mycol = mydb[collection]
    mycol.delete_many({})
    x = mycol.insert_many(documents)
    print(x.inserted_ids)

if __name__ == '__main__':
    li = readFile('./nodes.xls')
    treeLi = genTree(li)
    saveTodb('zhuzhoucheng',treeLi)

